package com.cypher.encryptionanddecryption.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Map;

/**
 * 数据库连接工具类
 * @author: 古霸天 
 * @date: 2020/11/26
 */
public class DbUtils {
    private final static String JDBC_URL = "jdbc:jtds:sqlserver://;DatabaseName=SQLDB001";

    private static Connection conn;
    /**
     * 获取JDBC连接
     * @return
     */
    public static Connection getConnection() {
        Connection conn = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            conn = DriverManager.getConnection(JDBC_URL, "sa" , "Sa123456");
        }  catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return conn;
    }

    /**
     * 获取预编译SQL声明
     * @param sql 需执行的SQL语句
     * @param params
     *      需要传的参数；采用Map类型传递
     *      key存放参数位置，为int类型
     *      value存放参数值，为object类型
     * @return
     */
    public static PreparedStatement getPreparedStatement(String sql, Map<Integer, Object> params) {
        PreparedStatement ps = null;
        try {
            conn = getConnection();
            if (conn == null) return null;
            // 预编译SQL
            ps = conn.prepareStatement(sql);

            // 采用迭代器遍历Map
            // 循环给SQL赋值
            if (params != null && params.size() > 0) {
                Iterator<Map.Entry<Integer, Object>> iterator = params.entrySet().iterator();
                while (iterator.hasNext()) {
                    Map.Entry<Integer, Object> entry = iterator.next();
                    ps.setObject(entry.getKey(), entry.getValue());
                }
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }
        return ps;
    }

    /**
     * 关闭JDBC连接
     * @param ps
     * @param rs
     */
    public static void closeConn(PreparedStatement ps, ResultSet rs) {
        try {
            rs.close();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    /**
     * 关闭jdbc连接
     * @param conn
     * @param ps
     * @param rs
     */
    public static void closeConn(Connection conn, PreparedStatement ps, ResultSet rs) {
        try {
            rs.close();
            ps.close();
            conn.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}